import os
from pathlib import Path
import time

import platform
import jaydebeapi
import jpype
import jpype.imports
from jpype.types import *
import subprocess
import pyodbc, io

import numpy as np
import pandas as pd
import warnings
warnings.filterwarnings("ignore")

from utils.logging_config import setup_logger

logger = setup_logger('Load Access Data')

# 加载access数据库文件
def start_jvm(jars):
    """启动 JVM（如果尚未启动）"""
    if not jpype.isJVMStarted():
        # 准备类路径
        classpath = ":".join([str(jar) for jar in jars])
        if platform.system() == "Windows":
            classpath = ";".join([str(jar) for jar in jars])
        
        # 添加额外的内存参数
        jvm_args = [
            "-Xms1024m",  # 初始堆内存
            "-Xmx16384m", # 最大堆内存16G
            f"-Djava.class.path={classpath}",
            "-XX:+UseG1GC",                 # 使用G1垃圾回收器
            # "-XX:MaxHeapFreeRatio=70",      # 堆空闲后最大保留比例
            # "-XX:MinHeapFreeRatio=30",      # 堆空闲后最小保留比例
            # "-XX:+AggressiveHeap",          # 对内存敏感应用优化
            # "-XX:+AlwaysPreTouch",          # 启动时预分配内存
            "-Dfile.encoding=UTF-8"
        ]
        
        try:
            jpype.startJVM(
                jpype.getDefaultJVMPath(),
                *jvm_args,
                convertStrings=True
            )
            logger.info("启动 JVM ")
        except Exception as e:
            logger.info(f"启动 JVM 失败: {str(e)}")
            # 尝试在没有特定 JVM 路径的情况下启动
            jpype.startJVM(*jvm_args, convertStrings=True)
            logger.info("JVM 已启动（使用默认路径）")

def check_jarfiles(jar_dir):
    # 必需的JAR文件
    jar_path = Path(jar_dir).resolve()
    jars = [
        os.path.join(jar_path, 'ucanaccess-5.0.1.jar'),
        os.path.join(jar_path, 'hsqldb-2.5.0.jar'),
        os.path.join(jar_path, 'jackcess-3.0.1.jar'),
        os.path.join(jar_path, 'commons-lang3-3.8.1.jar'),
        os.path.join(jar_path, 'commons-logging-1.2.jar')
    ]
    
    # 检查所有JAR文件是否存在
    missing_jars = [j for j in jars if not os.path.exists(j)]
    if missing_jars:
        raise FileNotFoundError(f"缺少JAR文件: {', '.join(missing_jars)}")
    return jars

def connect_access(db_path, jar_dir):
    jars = check_jarfiles(jar_dir)
    if not jpype.isJVMStarted():
        start_jvm(jars)
    logger.info(f"JVM 已启动: {jpype.isJVMStarted()}")

    # 导入 Java 类
    DriverManager = jpype.JClass("java.sql.DriverManager")
    UcanaccessDriver = jpype.JClass("net.ucanaccess.jdbc.UcanaccessDriver")\
    
    driver_instance = UcanaccessDriver()
    DriverManager.registerDriver(driver_instance)

    # 创建 JDBC 连接字符串
    jdbc_url = f"jdbc:ucanaccess://{db_path};immediatelyLoad=true;showSchema=true"
    # jdbc_url = f"jdbc:ucanaccess://{db_path};"
    # jdbc_url += "immediatelyLoad=false;"  # 不要立即加载全部数据
    # jdbc_url += "memory=false;"  # 减少内存使用
    # jdbc_url += "jackcessOpener=org.relique.jdbc.opener.NonPooledOpener;"  # 避免连接池
    # jdbc_url += "showSchema=true"

    conn = DriverManager.getConnection(jdbc_url)
    stmt = conn.createStatement()
    return conn, stmt

def get_table_names(conn, stmt):
    """获取所有表名"""
    if not conn:
        raise ConnectionError("未连接到数据库")
    try:
        start=time.time()
        meta = conn.getMetaData()
        result_set = meta.getTables(None, None, None, ["TABLE"])
        
        tables = []
        while result_set.next():
            table_name = result_set.getString("TABLE_NAME")
            # 过滤掉系统表
            if not table_name.startswith("MSys"):
                tables.append(table_name)
        end=time.time()
        logger.info(f'This step takes {round(end-start)} seconds')
        return tables
    except jpype.JException as e:
        logger.error(f"获取表名失败: {str(e)}")
        return []
    finally:
        # 关闭连接
        stmt.close()
        stmt = None
        conn.close()
        conn = None

def get_value(result_set, index):
    """安全获取值，处理 NULL 情况"""
    try:
        value = result_set.getObject(index)
        return value if not result_set.wasNull() else None
    except jpype.JException:
        return None

def execute_query(query, conn, stmt):
    """执行自定义 SQL 查询"""
    if not conn:
        raise ConnectionError("未连接到数据库")
    
    try:
        result_set = stmt.executeQuery(query)
        
        # 获取列信息
        meta = result_set.getMetaData()
        column_count = meta.getColumnCount()
        columns = [meta.getColumnName(i + 1) for i in range(column_count)]
        
        # 获取数据
        data = []
        while result_set.next():
            row = [get_value(result_set, i + 1) for i in range(column_count)]
            data.append(row)
        return pd.DataFrame(data, columns=columns)
    except jpype.JException as e:
        logger.error(f"查询执行失败: {str(e)}\n查询: {query}")
        return pd.DataFrame()

def load_in_chunks(result_set, column_count, columns, chunk_size):
    """分块加载大型表"""
    start=time.time()
    chunks = []
    row_count = 0
    
    while True:
        chunk_data = []
        for _ in range(chunk_size):
            if not result_set.next():
                break
            row = [get_value(result_set, i + 1) for i in range(column_count)]
            chunk_data.append(row)
            row_count += 1
        
        if not chunk_data:
            break
        
        chunks.append(pd.DataFrame(chunk_data, columns=columns))
        logger.info(f"已加载 {row_count} 行")

    df = pd.concat(chunks, ignore_index=True) if chunks else pd.DataFrame()
    end=time.time()
    logger.info(f'This step takes {round(end-start)} seconds')
    return df

def load_table(table_name, conn, stmt, chunk_size=None):
    """
    加载表数据到 Pandas DataFrame
    
    :param table_name: 表名
    :param chunk_size: 分块大小 (用于大型表)
    :return: DataFrame 包含表数据
    """
    if not conn:
        raise ConnectionError("未连接到数据库")
    
    try:
        start=time.time()
        # 执行查询
        query = f'SELECT * FROM [{table_name}]'
        result_set = stmt.executeQuery(query)
        
        # 获取列信息
        meta = result_set.getMetaData()
        column_count = meta.getColumnCount()
        columns = [meta.getColumnName(i + 1) for i in range(column_count)]
        
        # 分块处理大型表
        if chunk_size:
            return load_in_chunks(result_set, column_count, columns, chunk_size)
        
        # 一次性加载小型表
        data = []
        while result_set.next():
            row = [get_value(result_set, i + 1) for i in range(column_count)]
            data.append(row)
        
        pd.DataFrame(data, columns=columns)
        end=time.time()
        logger.info(f'This step takes {round(end-start)} seconds')
        return df
    except jpype.JException as e:
        logger.error(f"加载表 '{table_name}' 失败: {str(e)}")
        return pd.DataFrame()
    finally:
        # 关闭连接
        stmt.close()
        stmt = None
        conn.close()
        conn = None

def read_mdb_table(mdb_path, table_name):
    system = platform.system()
    if system == 'Windows':
        # Windows 实现
        conn_str = f'DRIVER={{Microsoft Access Driver (*.mdb)}};DBQ={mdb_path};'
        try:
            conn = pyodbc.connect(conn_str)
            return pd.read_sql(f'SELECT * FROM [{table_name}]', conn)
        finally:
            if 'conn' in locals():
                conn.close()
    elif system == 'Linux':
        # Linux/Ubuntu 实现
        try:
            cmd = f'mdb-export "{mdb_path}" "{table_name}"'
            result = subprocess.run(cmd, shell=True, capture_output=True, text=True, check=True)
            return pd.read_csv(io.StringIO(result.stdout))
        except subprocess.CalledProcessError as e:
            raise Exception(f"mdb-export failed: {e.stderr}")
    else:
        raise OSError(f"Unsupported operating system: {system}")
    


